package zy.dao.batch.sell.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.batch.sell.BatchSellDAO;
import zy.dto.common.ProductDto;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.size.T_Base_Size;
import zy.entity.base.stream.T_Base_Stream;
import zy.entity.batch.client.T_Batch_ClientPrice;
import zy.entity.batch.order.T_Batch_Import;
import zy.entity.batch.order.T_Batch_Order;
import zy.entity.batch.sell.T_Batch_Sell;
import zy.entity.batch.sell.T_Batch_SellList;
import zy.entity.stock.data.T_Stock_DataBill;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class BatchSellDAOImpl extends BaseDaoImpl implements BatchSellDAO{

	@Override
	public Map<String, Object> countSum(Map<String, Object> params) {
		Object se_type = params.get("se_type");
		Object se_isdraft = params.get("se_isdraft");
		Object se_ar_state = params.get("se_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object se_client_code = params.get("se_client_code");
		Object se_depot_code = params.get("se_depot_code");
		Object se_manager = params.get("se_manager");
		Object se_number = params.get("se_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) AS totalCount,");
		sql.append(" IFNULL(SUM(ABS(se_amount)),0) AS se_amount,");
		sql.append(" IFNULL(SUM(ABS(se_money)),0) AS se_money,");
		sql.append(" IFNULL(SUM(ABS(se_discount_money)),0) AS se_discount_money,");
		sql.append(" IFNULL(SUM(ABS(se_rebatemoney)),0) AS se_rebatemoney,");
		sql.append(" IFNULL(SUM(ABS(se_stream_money)),0) AS se_stream_money,");
		sql.append(" IFNULL(SUM(ABS(se_receivable)),0) AS se_receivable,");
		sql.append(" IFNULL(SUM(ABS(se_retailmoney)),0) AS se_retailmoney");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
		if (StringUtil.isNotEmpty(se_type)) {
			sql.append(" AND se_type = :se_type ");
		}
		if (StringUtil.isNotEmpty(se_isdraft)) {
			sql.append(" AND se_isdraft = :se_isdraft ");
		}
		if (StringUtil.isNotEmpty(se_ar_state)) {
			sql.append(" AND se_ar_state = :se_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND se_make_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND se_make_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(se_client_code)) {
			sql.append(" AND se_client_code = :se_client_code ");
		}
		if (StringUtil.isNotEmpty(se_depot_code)) {
			sql.append(" AND se_depot_code = :se_depot_code ");
		}
		if (StringUtil.isNotEmpty(se_manager)) {
			sql.append(" AND se_manager = :se_manager ");
		}
		if (StringUtil.isNotEmpty(se_number)) {
			sql.append(" AND INSTR(se_number,:se_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<T_Batch_Sell> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object se_type = params.get("se_type");
		Object se_isdraft = params.get("se_isdraft");
		Object se_ar_state = params.get("se_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object se_client_code = params.get("se_client_code");
		Object se_depot_code = params.get("se_depot_code");
		Object se_manager = params.get("se_manager");
		Object se_number = params.get("se_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT se_id,se_number,se_make_date,se_client_code,se_client_shop_code,se_depot_code,se_maker,se_manager,se_handnumber,se_amount,");
		sql.append(" se_money,se_retailmoney,se_costmoney,se_rebatemoney,se_discount_money,se_ba_code,se_property,se_remark,");
		sql.append(" se_ar_state,se_ar_date,se_isdraft,se_pay_state,se_receivable,se_received,se_prepay,se_order_number,se_type,");
		sql.append(" se_stream_code,se_stream_money,se_lastdebt,se_sysdate,se_us_id,se_isprint,t.companyid,ci_name AS client_name,");
		sql.append(" (SELECT cis_name FROM t_batch_client_shop cis WHERE cis.cis_code = t.se_client_shop_code AND cis.cis_ci_code = t.se_client_code AND cis.companyid = t.companyid LIMIT 1) AS client_shop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = se_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
		if (StringUtil.isNotEmpty(se_type)) {
			sql.append(" AND se_type = :se_type ");
		}
		if (StringUtil.isNotEmpty(se_isdraft)) {
			sql.append(" AND se_isdraft = :se_isdraft ");
		}
		if (StringUtil.isNotEmpty(se_ar_state)) {
			sql.append(" AND se_ar_state = :se_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND se_make_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND se_make_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(se_client_code)) {
			sql.append(" AND se_client_code = :se_client_code ");
		}
		if (StringUtil.isNotEmpty(se_depot_code)) {
			sql.append(" AND se_depot_code = :se_depot_code ");
		}
		if (StringUtil.isNotEmpty(se_manager)) {
			sql.append(" AND se_manager = :se_manager ");
		}
		if (StringUtil.isNotEmpty(se_number)) {
			sql.append(" AND INSTR(se_number,:se_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY se_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Batch_Sell.class));
	}

	@Override
	public T_Batch_Sell load(Integer se_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT se_id,se_number,se_make_date,se_client_code,se_client_shop_code,se_depot_code,se_maker,se_manager,se_handnumber,se_amount,");
		sql.append(" se_money,se_retailmoney,se_costmoney,se_rebatemoney,se_discount_money,se_ba_code,se_property,se_remark,");
		sql.append(" se_ar_state,se_ar_date,se_isdraft,se_pay_state,se_receivable,se_received,se_prepay,se_order_number,se_type,");
		sql.append(" se_stream_code,se_stream_money,se_lastdebt,se_sysdate,se_us_id,se_isprint,t.companyid,ci_name AS client_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = se_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name,");
		sql.append(" (SELECT cis_name FROM t_batch_client_shop cis WHERE cis.cis_code = t.se_client_shop_code AND cis.cis_ci_code = t.se_client_code AND cis.companyid = t.companyid LIMIT 1) AS client_shop_name,");
		sql.append(" (SELECT se_name FROM t_base_stream se WHERE se_code = se_stream_code AND se.companyid = t.companyid LIMIT 1) AS stream_name");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE se_id = :se_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("se_id", se_id),
					new BeanPropertyRowMapper<>(T_Batch_Sell.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Batch_Sell load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT se_id,se_number,se_make_date,se_client_code,se_client_shop_code,se_depot_code,se_maker,se_manager,se_handnumber,se_amount,");
		sql.append(" se_money,se_retailmoney,se_costmoney,se_rebatemoney,se_discount_money,se_ba_code,se_property,se_remark,");
		sql.append(" se_ar_state,se_ar_date,se_isdraft,se_pay_state,se_receivable,se_received,se_prepay,se_order_number,se_type,");
		sql.append(" se_stream_code,se_stream_money,se_lastdebt,se_sysdate,se_us_id,se_isprint,t.companyid,ci_name AS client_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = se_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name,");
		sql.append(" (SELECT cis_name FROM t_batch_client_shop cis WHERE cis.cis_code = t.se_client_shop_code AND cis.cis_ci_code = t.se_client_code AND cis.companyid = t.companyid LIMIT 1) AS client_shop_name,");
		sql.append(" (SELECT se_name FROM t_base_stream se WHERE se_code = se_stream_code AND se.companyid = t.companyid LIMIT 1) AS stream_name");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE se_number = :se_number AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("se_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Batch_Sell.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Batch_Sell check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT se_id,se_number,se_client_code,se_depot_code,se_ar_state,se_pay_state,se_type,se_amount,se_discount_money,se_receivable,");
		sql.append(" se_stream_code,se_stream_money,se_remark,companyid");
		sql.append(" FROM t_batch_sell t");
		sql.append(" WHERE se_number = :se_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("se_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Batch_Sell.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Batch_SellList> detail_list_forsavetemp(String se_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sel_id,sel_number,sel_pd_code,sel_sub_code,sel_sz_code,sel_szg_code,sel_cr_code,sel_br_code,sel_amount,sel_unitprice,");
		sql.append(" sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_order_number,sel_pi_type,sel_type,t.companyid ");
		sql.append(" FROM t_batch_selllist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_number = :sel_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY sel_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("sel_number", se_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}
	
	@Override
	public List<T_Batch_SellList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		
		sql.append(" SELECT sel_id,sel_number,sel_pd_code,sel_sub_code,sel_sz_code,sel_szg_code,sel_cr_code,sel_br_code,sel_amount,sel_unitprice,");
		sql.append(" sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_order_number,sel_pi_type,sel_type,t.companyid,pd_no,pd_name,pd_unit, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sel_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sel_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sel_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_batch_selllist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.sel_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_number = :sel_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sel_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}
	
	@Override
	public List<T_Batch_SellList> detail_list_print(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sel_id,sel_number,sel_pd_code,sel_sub_code,sel_sz_code,sel_szg_code,sel_cr_code,sel_br_code,sel_amount,sel_unitprice,");
		sql.append(" sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_order_number,sel_pi_type,sel_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sel_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sel_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sel_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_batch_selllist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.sel_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_number = :sel_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sel_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}
	
	@Override
	public List<T_Batch_SellList> detail_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sel_id,sel_number,sel_pd_code,sel_sub_code,sel_sz_code,sel_szg_code,sel_cr_code,sel_br_code,SUM(sel_amount) AS sel_amount,");
		sql.append(" sel_unitprice,sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_pi_type,sel_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_batch_selllist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.sel_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_number = :sel_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY sel_pd_code,sel_pi_type");
		sql.append(" ORDER BY sel_pd_code,sel_pi_type ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}
	
	@Override
	public List<String> detail_szgcode(Map<String,Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT sel_szg_code");
		sql.append(" FROM t_batch_selllist t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND sel_number = :sel_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public List<T_Batch_SellList> list_order_forimport(List<Long> ids) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT odl_number AS sel_order_number,odl_pd_code AS sel_pd_code,odl_sub_code AS sel_sub_code,odl_szg_code AS sel_szg_code,");
		sql.append(" odl_sz_code AS sel_sz_code,odl_cr_code AS sel_cr_code,odl_br_code AS sel_br_code,odl_amount-odl_realamount AS sel_amount,");
		sql.append(" odl_unitprice AS sel_unitprice,odl_retailprice AS sel_retailprice,odl_costprice AS sel_costprice,0 AS sel_rebateprice,odl_pi_type AS sel_pi_type,odl_type AS sel_type,companyid");
		sql.append(" FROM t_batch_orderlist");
		sql.append(" WHERE odl_id IN(:ids)");
		sql.append(" AND odl_amount > odl_realamount");
		return namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("ids", ids),
				new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}
	
	@Override
	public List<T_Batch_SellList> temp_list_forimport(Integer se_type,Integer us_id,Integer companyid) {//只查询商品
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sel_id,sel_pd_code,sel_sub_code,sel_sz_code,sel_szg_code,sel_cr_code,sel_br_code,sel_amount,sel_unitprice,");
		sql.append(" sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_order_number,sel_pi_type,sel_us_id,sel_type,t.companyid ");
		sql.append(" FROM t_batch_selllist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("sel_pi_type", 0).addValue("sel_type", se_type).addValue("sel_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}
	
	@Override
	public List<T_Batch_SellList> temp_list_forsave(Integer se_type,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sel_id,sel_pd_code,sel_sub_code,sel_sz_code,sel_szg_code,sel_cr_code,sel_br_code,sel_amount,sel_unitprice,");
		sql.append(" sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_order_number,sel_pi_type,sel_us_id,sel_type,t.companyid ");
		sql.append(" FROM t_batch_selllist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY sel_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("sel_type", se_type).addValue("sel_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}
	
	@Override
	public List<T_Batch_SellList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sel_id,sel_pd_code,sel_sub_code,sel_sz_code,sel_szg_code,sel_cr_code,sel_br_code,sel_amount,sel_unitprice,");
		sql.append(" sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_order_number,sel_pi_type,sel_us_id,sel_type,t.companyid,pd_no,pd_name,pd_unit, ");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sel_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sel_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sel_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_batch_selllist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.sel_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sel_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}

	@Override
	public List<T_Batch_SellList> temp_sum(Integer se_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sel_id,sel_pd_code,sel_szg_code,SUM(sel_amount) AS sel_amount,sel_unitprice, sel_retailprice,sel_costprice,sel_rebateprice,");
		sql.append(" sel_remark,sel_pi_type,sel_us_id,sel_type,t.companyid,pd_no,pd_name,pd_unit,  ");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_batch_selllist_temp t ");
		sql.append(" JOIN t_base_product pd ON pd_code = t.sel_pd_code AND pd.companyid = t.companyid");  
		sql.append(" WHERE 1=1 ");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY sel_pd_code,sel_pi_type");
		sql.append(" ORDER BY sel_pd_code,sel_pi_type ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("sel_type", se_type).addValue("sel_us_id", us_id).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}

	@Override
	public List<String> temp_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT sel_szg_code");
		sql.append(" FROM t_batch_selllist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public Integer count_product(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT count(1)");
		sql.append(" FROM (");
		sql.append(" SELECT 1 FROM t_base_product t");
		sql.append(" LEFT JOIN t_batch_selllist_temp sel ON sel_pd_code = pd_code AND sel.companyid = t.companyid AND sel_type = :se_type AND sel_us_id = :us_id");
		sql.append(" where 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND sel_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code)t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
	}

	@Override
	public List<T_Base_Product> list_product(Map<String, Object> param) {
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_id,pd_code,pd_no,pd_name,IF(sel_id IS NULL,0,1) AS exist");
		sql.append(" FROM t_base_product t");
		sql.append(" LEFT JOIN t_batch_selllist_temp sel ON sel_pd_code = pd_code AND sel.companyid = t.companyid AND sel_type = :se_type AND sel_us_id = :us_id");
		sql.append(" where 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND sel_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pd_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Product.class));
	}

	@Override
	public T_Base_Product load_product(String pd_code,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pd.pd_id,pd.pd_code,pd_no,pd_name,pd_szg_code,pd_unit,pd_year,pd_season,pd_sell_price,pd_cost_price,");
		sql.append(" IFNULL(pd_batch_price,0) AS pd_batch_price,");
		sql.append(" IFNULL(pd_batch_price1,0) AS pd_batch_price1,");
		sql.append(" IFNULL(pd_batch_price2,0) AS pd_batch_price2,");
		sql.append(" IFNULL(pd_batch_price3,0) AS pd_batch_price3,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = pd.companyid LIMIT 1) AS pd_bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = pd.companyid LIMIT 1) AS pd_tp_name");
		sql.append(" ,(SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm_pd_code = pd.pd_code AND pdm.companyid = pd.companyid LIMIT 1) AS pdm_img_path");
		sql.append(" FROM t_base_product pd");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd.pd_code = :pd_code");
		sql.append(" AND pd.companyid = :companyid");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Product.class));
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	@Override
	public Double queryLastBatchPrice(String pd_code,String ci_code, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cp_price");
		sql.append(" FROM t_batch_clientprice");
		sql.append(" WHERE cp_pd_code = :pd_code");
		sql.append(" AND cp_ci_code = :ci_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("ci_code", ci_code).addValue("companyid", companyid), Double.class);
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public Map<String, Object> load_product_size(Map<String,Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//1.查询尺码信息
		List<T_Base_Size> sizes = namedParameterJdbcTemplate.query(getSizeSQL(), params, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizes);
		//2.获取颜色杯型信息
		List<ProductDto> inputs = namedParameterJdbcTemplate.query(getColorBraSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("inputs",inputs);
		//3.已录入数量
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sel_sz_code AS sz_code,sel_cr_code AS cr_code,sel_br_code AS br_code,sel_amount AS amount");
		sql.append(" FROM t_batch_selllist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_pd_code = :pd_code");
		sql.append(" AND sel_us_id = :us_id");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :se_type");
		sql.append(" AND companyid = :companyid");
		List<ProductDto> temps = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("temps",temps);
		//4.库存数量
		List<ProductDto> stocks = namedParameterJdbcTemplate.query(getStockSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("stocks",stocks);
		return resultMap;
	}
	
	@Override
	public T_Batch_SellList temp_queryUnitPrice(String pd_code,String sel_pi_type, Integer se_type, Integer us_id, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sel_unitprice,sel_rebateprice");
		sql.append(" FROM t_batch_selllist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_pd_code = :sel_pd_code");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("sel_pd_code", pd_code)
							.addValue("sel_type", se_type)
							.addValue("sel_pi_type", sel_pi_type)
							.addValue("sel_us_id", us_id)
							.addValue("companyid", companyid), 
					new BeanPropertyRowMapper<>(T_Batch_SellList.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Batch_SellList temp_loadBySubCode(String sub_code,String sel_pi_type, Integer se_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		
		sql.append(" SELECT sel_id,sel_pd_code,sel_sub_code,sel_sz_code,sel_szg_code,sel_cr_code,sel_br_code,sel_amount,sel_unitprice,");
		sql.append(" sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_order_number,sel_pi_type,sel_us_id,sel_type,t.companyid");
		sql.append(" FROM t_batch_selllist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_sub_code = :sel_sub_code");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource()
							.addValue("sel_sub_code", sub_code)
							.addValue("sel_type", se_type)
							.addValue("sel_pi_type", sel_pi_type)
							.addValue("sel_us_id", us_id)
							.addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Batch_SellList.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public void temp_save(List<T_Batch_SellList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_batch_selllist_temp");
		sql.append(" (sel_pd_code,sel_sub_code,sel_szg_code,sel_sz_code,sel_cr_code,sel_br_code,sel_amount,");
		sql.append(" sel_unitprice,sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_pi_type,sel_order_number,sel_us_id,sel_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sel_pd_code,:sel_sub_code,:sel_szg_code,:sel_sz_code,:sel_cr_code,:sel_br_code,:sel_amount,");
		sql.append(" :sel_unitprice,:sel_retailprice,:sel_costprice,:sel_rebateprice,:sel_remark,:sel_pi_type,:sel_order_number,:sel_us_id,:sel_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_save(T_Batch_SellList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_batch_selllist_temp");
		sql.append(" (sel_pd_code,sel_sub_code,sel_szg_code,sel_sz_code,sel_cr_code,sel_br_code,sel_amount,");
		sql.append(" sel_unitprice,sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_pi_type,sel_order_number,sel_us_id,sel_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sel_pd_code,:sel_sub_code,:sel_szg_code,:sel_sz_code,:sel_cr_code,:sel_br_code,:sel_amount,");
		sql.append(" :sel_unitprice,:sel_retailprice,:sel_costprice,:sel_rebateprice,:sel_remark,:sel_pi_type,:sel_order_number,:sel_us_id,:sel_type,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp),holder);
		temp.setSel_id(holder.getKey().longValue());
	}

	@Override
	public void temp_update(List<T_Batch_SellList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_selllist_temp");
		sql.append(" SET sel_amount = :sel_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_sub_code = :sel_sub_code");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_updateById(List<T_Batch_SellList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_selllist_temp");
		sql.append(" SET sel_amount = :sel_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_id = :sel_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_update(T_Batch_SellList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_selllist_temp");
		sql.append(" SET sel_amount = :sel_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_id = :sel_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateprice(String pd_code,String sel_pi_type, Double unitPrice, Integer se_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_selllist_temp");
		sql.append(" SET sel_unitprice = :sel_unitprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_pd_code = :sel_pd_code");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource()
						.addValue("sel_unitprice", unitPrice)
						.addValue("sel_pd_code", pd_code)
						.addValue("sel_pi_type", sel_pi_type)
						.addValue("sel_type", se_type)
						.addValue("sel_us_id", us_id)
						.addValue("companyid", companyid));
	}
	
	@Override
	public void temp_updaterebateprice(String pd_code, Double rebatePrice, Integer se_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_selllist_temp");
		sql.append(" SET sel_rebateprice = :sel_rebateprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_pd_code = :sel_pd_code");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource()
						.addValue("sel_rebateprice", rebatePrice)
						.addValue("sel_pd_code", pd_code)
						.addValue("sel_pi_type", 0)
						.addValue("sel_type", se_type)
						.addValue("sel_us_id", us_id)
						.addValue("companyid", companyid));
	}

	@Override
	public void temp_updateRemarkById(T_Batch_SellList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_selllist_temp");
		sql.append(" SET sel_remark = :sel_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_id = :sel_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateRemarkByPdCode(T_Batch_SellList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_selllist_temp");
		sql.append(" SET sel_remark = :sel_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_pd_code = :sel_pd_code");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_del(List<T_Batch_SellList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_batch_selllist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_sub_code = :sel_sub_code");
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}

	@Override
	public void temp_del(Integer sel_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_batch_selllist_temp");
		sql.append(" WHERE sel_id=:sel_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sel_id", sel_id));
		
	}

	@Override
	public void temp_delByPiCode(T_Batch_SellList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_batch_selllist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_pd_code = :sel_pd_code");
		if(StringUtil.isNotEmpty(temp.getSel_cr_code())){
			sql.append(" AND sel_cr_code = :sel_cr_code");
		}
		if(StringUtil.isNotEmpty(temp.getSel_br_code())){
			sql.append(" AND sel_br_code = :sel_br_code");
		}
		sql.append(" AND sel_pi_type = :sel_pi_type");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_clear(Integer se_type,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_batch_selllist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_type = :sel_type");
		sql.append(" AND sel_us_id = :sel_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("sel_type", se_type)
						.addValue("sel_us_id", us_id)
						.addValue("companyid", companyid));
	}
	
	@Override
	public List<T_Batch_Import> temp_listByImport(List<String> barCodes,String priceType,Double ci_rate,String batch_price,String ci_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT DISTINCT bc_pd_code,bc_subcode,bc_barcode,bc_size,pd_szg_code,bc_color,bc_bra,");
		sql.append(" pd_sell_price AS retail_price,");
		sql.append(" pd_cost_price AS cost_price,");
		if("1".equals(priceType)){//最近批发价
			sql.append(" IFNULL((SELECT cp_price FROM t_batch_clientprice cp");
			sql.append(" WHERE cp_pd_code = bc_pd_code AND cp_ci_code = :ci_code AND cp.companyid = bc.companyid),");
			sql.append(" IFNULL(pd_batch_price,0)) AS unit_price");
		}else if("2".equals(priceType)){//按照折扣率
			sql.append(" :ci_rate*pd_sell_price AS unit_price");
		}else if("3".equals(priceType)){//批发价
			if("0".equals(batch_price)){
				sql.append("pd_batch_price AS unit_price");
			}else if("1".equals(batch_price)){
				sql.append("pd_batch_price AS unit_price1");
			}else if("2".equals(batch_price)){
				sql.append("pd_batch_price AS unit_price2");
			}else if("3".equals(batch_price)){
				sql.append("pd_batch_price AS unit_price3");
			}else {
				sql.append("pd_batch_price AS unit_price");
			}
		}
		sql.append(" FROM t_base_barcode bc");
		sql.append(" JOIN t_base_product pd ON pd.pd_code = bc_pd_code AND pd.companyid = bc.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND bc_barcode IN(:barcode)");
		sql.append(" AND bc.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("barcode", barCodes)
						.addValue("ci_rate", ci_rate)
						.addValue("ci_code", ci_code)
						.addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Batch_Import.class));
	}
	
	@Override
	public void save(T_Batch_Sell sell, List<T_Batch_SellList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_BATCH_SELL_OUT + DateUtil.getYearMonthDateYYYYMMDD();
		if(sell.getSe_type().equals(1)){
			prefix = CommonUtil.NUMBER_PREFIX_BATCH_SELL_IN + DateUtil.getYearMonthDateYYYYMMDD();
		}
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(se_number))) AS new_number");
		sql.append(" FROM t_batch_sell");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(se_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", sell.getCompanyid()), String.class);
		sell.setSe_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_batch_sell");
		sql.append(" (se_number,se_make_date,se_client_code,se_client_shop_code,se_depot_code,se_maker,se_manager,se_handnumber,se_amount,");
		sql.append(" se_money,se_retailmoney,se_costmoney,se_rebatemoney,se_discount_money,se_ba_code,se_property,");
		sql.append(" se_remark,se_ar_state,se_ar_date,se_isdraft,se_pay_state,se_receivable,se_received,se_prepay,se_order_number,");
		sql.append(" se_stream_code,se_stream_money,se_lastdebt,se_sysdate,se_us_id,se_type,se_isprint,companyid)");
		sql.append(" VALUES");
		sql.append(" (:se_number,:se_make_date,:se_client_code,:se_client_shop_code,:se_depot_code,:se_maker,:se_manager,:se_handnumber,:se_amount,");
		sql.append(" :se_money,:se_retailmoney,:se_costmoney,:se_rebatemoney,:se_discount_money,:se_ba_code,:se_property,");
		sql.append(":se_remark,:se_ar_state,:se_ar_date,:se_isdraft,:se_pay_state,:se_receivable,:se_received,:se_prepay,:se_order_number,");
		sql.append(" :se_stream_code,:se_stream_money,:se_lastdebt,:se_sysdate,:se_us_id,:se_type,:se_isprint,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(sell),holder);
		sell.setSe_id(holder.getKey().intValue());
		for(T_Batch_SellList item:details){
			item.setSel_number(sell.getSe_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_batch_selllist");
		sql.append(" (sel_number,sel_pd_code,sel_sub_code,sel_szg_code,sel_sz_code,sel_cr_code,sel_br_code,sel_amount,");
		sql.append(" sel_unitprice,sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_pi_type,sel_order_number,sel_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sel_number,:sel_pd_code,:sel_sub_code,:sel_szg_code,:sel_sz_code,:sel_cr_code,:sel_br_code,:sel_amount,");
		sql.append(" :sel_unitprice,:sel_retailprice,:sel_costprice,:sel_rebateprice,:sel_remark,:sel_pi_type,:sel_order_number,:sel_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Batch_Sell sell, List<T_Batch_SellList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_batch_sell");
		sql.append(" SET se_make_date=:se_make_date");
		sql.append(" ,se_client_code=:se_client_code");
		sql.append(" ,se_client_shop_code=:se_client_shop_code");
		sql.append(" ,se_depot_code=:se_depot_code");
		sql.append(" ,se_maker=:se_maker");
		sql.append(" ,se_manager=:se_manager");
		sql.append(" ,se_handnumber=:se_handnumber");
		sql.append(" ,se_amount=:se_amount");
		sql.append(" ,se_money=:se_money");
		sql.append(" ,se_retailmoney=:se_retailmoney");
		sql.append(" ,se_costmoney=:se_costmoney");
		sql.append(" ,se_rebatemoney=:se_rebatemoney");
		sql.append(" ,se_discount_money=:se_discount_money");
		sql.append(" ,se_receivable=:se_receivable");
		sql.append(" ,se_stream_code=:se_stream_code");
		sql.append(" ,se_stream_money=:se_stream_money");
		sql.append(" ,se_property=:se_property");
		sql.append(" ,se_remark=:se_remark");
		sql.append(" ,se_ar_state=:se_ar_state");
		sql.append(" ,se_ar_date=:se_ar_date");
		sql.append(" ,se_order_number=:se_order_number");
		sql.append(" ,se_us_id=:se_us_id");
		sql.append(" ,se_isprint=:se_isprint");
		sql.append(" WHERE se_id=:se_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(sell));
		for(T_Batch_SellList item:details){
			item.setSel_number(sell.getSe_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_batch_selllist");
		sql.append(" (sel_number,sel_pd_code,sel_sub_code,sel_szg_code,sel_sz_code,sel_cr_code,sel_br_code,sel_amount,");
		sql.append(" sel_unitprice,sel_retailprice,sel_costprice,sel_rebateprice,sel_remark,sel_pi_type,sel_order_number,sel_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sel_number,:sel_pd_code,:sel_sub_code,:sel_szg_code,:sel_sz_code,:sel_cr_code,:sel_br_code,:sel_amount,");
		sql.append(" :sel_unitprice,:sel_retailprice,:sel_costprice,:sel_rebateprice,:sel_remark,:sel_pi_type,:sel_order_number,:sel_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Batch_Sell sell) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_batch_sell");
		sql.append(" SET se_ar_state=:se_ar_state");
		sql.append(" ,se_ar_date = :se_ar_date");
		sql.append(" ,se_lastdebt = :se_lastdebt");
		sql.append(" WHERE se_id=:se_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(sell));
	}
	
	@Override
	public List<T_Batch_SellList> listWithOrder(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sel_number,sel_sub_code,ABS(sel_amount) AS sel_amount,sel_pi_type,sel_order_number,companyid");
		sql.append(" FROM t_batch_selllist");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_number = :sel_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" AND sel_order_number IS NOT NULL");
		sql.append(" AND sel_order_number != ''");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("sel_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Batch_SellList.class));
	}
	
	@Override
	public T_Batch_Order loadOrder(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT od_id,od_number,od_amount,od_realamount,od_delivery_date");
		sql.append(" FROM t_batch_order t");
		sql.append(" WHERE od_number = :od_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("od_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Batch_Order.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void updateOrder(T_Batch_Order order) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_batch_order");
		sql.append(" SET od_state=:od_state");
		sql.append(" ,od_realamount = :od_realamount");
		sql.append(" WHERE od_id=:od_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(order));
	}
	
	@Override
	public void updateOrderList(List<T_Batch_SellList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_orderlist");
		sql.append(" SET odl_realamount = odl_realamount + :sel_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND odl_sub_code = :sel_sub_code");
		sql.append(" AND odl_pi_type = :sel_pi_type");
		sql.append(" AND odl_number = :sel_order_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateOrderList_Reverse(List<T_Batch_SellList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_orderlist");
		sql.append(" SET odl_realamount = odl_realamount - :sel_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND odl_sub_code = :sel_sub_code");
		sql.append(" AND odl_pi_type = :sel_pi_type");
		sql.append(" AND odl_number = :sel_order_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public List<T_Stock_DataBill> listStock(String number,String dp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sel_pd_code AS sd_pd_code,sel_sub_code AS sd_code,sel_cr_code AS sd_cr_code,sel_sz_code AS sd_sz_code,");
		sql.append(" sel_br_code AS sd_br_code,SUM(ABS(sel_amount)) AS bill_amount,sd_id,sd_amount");
		sql.append(" FROM t_batch_selllist sel");
		sql.append(" LEFT JOIN t_stock_data sd ON sel_sub_code = sd_code AND sel.companyid = sd.companyid AND sd.sd_dp_code = :dp_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_number = :sel_number");
		sql.append(" AND sel.companyid = :companyid");
		sql.append(" GROUP BY sel_sub_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("sel_number", number).addValue("dp_code", dp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_DataBill.class));
	}
	
	@Override
	public List<T_Batch_ClientPrice> detail_listClientPrice(String number, Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sel_pd_code AS cp_pd_code,sel_unitprice AS cp_price");
		sql.append(" FROM t_batch_selllist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sel_pi_type = :sel_pi_type");//查询商品
		sql.append(" AND sel_number = :sel_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY sel_pd_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("sel_pi_type", 0).addValue("sel_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Batch_ClientPrice.class));
	}
	
	@Override
	public void saveClientPrice(List<T_Batch_ClientPrice> clientPrices) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_batch_clientprice");
		sql.append(" (cp_pd_code,cp_ci_code,cp_price,cp_sysdate,companyid)");
		sql.append(" VALUES(:cp_pd_code,:cp_ci_code,:cp_price,:cp_sysdate,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(clientPrices.toArray()));
	}
	
	@Override
	public void deleteClientPrice(List<T_Batch_ClientPrice> clientPrices) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_batch_clientprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND cp_id < (");
		sql.append(" SELECT MIN(cp_id) FROM");
		sql.append(" (SELECT cp.cp_id FROM t_batch_clientprice cp");
		sql.append(" WHERE cp.cp_pd_code = :cp_pd_code");
		sql.append(" AND cp.cp_ci_code = :cp_ci_code");
		sql.append(" AND cp.companyid = :companyid");
		sql.append(" ORDER BY cp.cp_id DESC LIMIT 10");
		sql.append(" )tmp");
		sql.append(" )");
		sql.append(" AND cp_pd_code = :cp_pd_code");
		sql.append(" AND cp_ci_code = :cp_ci_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(clientPrices.toArray()));
	}
	
	@Override
	public T_Base_Stream loadStream(String se_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT se_id,se_code,se_payable");
		sql.append(" FROM t_base_stream t");
		sql.append(" WHERE se_code = :se_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("se_code", se_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Stream.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void updateStream(T_Base_Stream stream) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_base_stream SET se_payable=:se_payable WHERE se_id=:se_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(stream));
	}
	
	@Override
	public void del(String od_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_batch_sell");
		sql.append(" WHERE se_number=:se_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("se_number", od_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_batch_selllist");
		sql.append(" WHERE sel_number=:sel_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sel_number", od_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String od_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_batch_selllist");
		sql.append(" WHERE sel_number=:sel_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sel_number", od_number).addValue("companyid", companyid));
	}
	
}
